ALTER PROCEDURE [dbo].[Proc_S2_DealDataToDocum_A00014]
AS
begin
	Begin Try
	declare @t_id int
	declare @t_yhbh varchar(20)		--用户编号
	declare @t_ysdz varchar(200)		--用水地址
	declare	@t_sjhm varchar(30)		--手机号码
	declare @t_ysl int				--用水量
	declare @t_bysf decimal(12,2)	--水费
	declare @t_bypsf decimal(12,2)	--污水费
	declare @t_byljf decimal(12,2)	--垃圾处理费
	declare @t_byje decimal(12,2)	--拖欠总金额（水费，污水费，垃圾处理费）
	declare @t_tqje decimal(12,2)	--历史拖欠金额
	declare @t_cbzt decimal(2)	--抄表状态（ number(2)暂定4是估抄）
	declare @t_jmje decimal(12,2)	--疫情减免金额
	DECLARE @t_recordstr varchar(2000) --0714 不合并 短信里面，UCIS对用户的水表数做判断，内容放在recordstr字段
	declare @t_lyear varchar(4),@t_lmonth varchar(4),@t_lday varchar(4)
	declare @t_year varchar(4),@t_month varchar(4),@t_day varchar(4)
	declare @t_smsinfo varchar(2000),@t_smsguid varchar(36)
	declare @t_usercodecount int,@t_usercount int,@t_bysf_sum decimal(12,2),@t_bypsf_sum decimal(12,2),@t_byljf_sum decimal(12,2),@t_byje_sum decimal(12,2),@t_tqje_sum decimal(12,2),@t_bhgs_sum int,@t_fyhj decimal(12,2),@t_jmje_sum decimal(12,2),@t_yhqsf_sum decimal(12,2)
	declare @g_status int
	declare @doc_count int
	declare @cj_count int
	declare @sms_guid varchar(36)
	declare @sql varchar(2000)
	declare @sql_count int,@i_smsnum int,@t_INTERVAL_LENGTH int,@t_amount int
	declare @t_itemid bigint
	declare @t_deptcode varchar(10),@c_batch varchar(2), @isHBNum int,@isGZNum int,@isJMJENum int
	DECLARE @tguid VARCHAR(36)
	DECLARE @sname VARCHAR(255)
	DECLARE @sms_content varchar(2000)
	DECLARE @sm_phone varchar(20)
	DECLARE @deptcode varchar(20)
	DECLARE @sms_content_time datetime

			--close cur_sjhm_a00014
			--deallocate cur_sjhm_a00014

	--检查是否有需要待处理的短信任务
	select @g_status=count(*) from ucis_sms_grab where type_id='A00014' and status1 is null
	if @g_status>0
	begin
		--获取待处理短信任务的编号
		select top 1 @sms_guid=sms_guid from ucis_sms_grab where TYPE_ID='A00014' and status1 is null
		if @sms_guid!=''
		begin
			--将要处理的任务标识开始处理
			update ucis_sms_monitoring set doc_starttime=GETDATE(),cj_starttime=GETDATE() where SMS_GUID=@sms_guid

			--初始化当前短信数量
			set @i_smsnum=0

			--定义游标循环处理
			declare cur_sjhm_a00014 cursor for Select Sjhm,
				       Count(*),
				       (Case
				         When Bhgs Is Null Or Bhgs = '' Then
				          1
				         Else
				          Bhgs
				       End) As Bhgscount,
				       Sum(Case
				             When Smsmix = '1' Then
				              1
				             Else
				              0
				           End) Ishbnum,
							Sum(Case
				             When ReadingStatus = '4' Then
				              1
				             Else
				              0
				           End) isGZNum,
							Sum(Case
				             When reducedMoney >0 Then
				              1
				             Else
				              0
				           End) isJMJENum
				  From Ucis_Sf_Message_Info
				 Where Sms_Guid = @sms_guid
				   And Sjhm Is Not Null
				   And Sjhm != ''
				   And Len(Sjhm) = 11
					 and  isgsjt= '1'
				   --And Sjhm = '13600163620'
				 Group By Sjhm,
				          (Case
				            When Bhgs Is Null Or Bhgs = '' Then
				             1
				            Else
				             Bhgs
				          End);

			--处理发送记录
			open cur_sjhm_a00014
			fetch next from cur_sjhm_a00014 into @t_sjhm, @t_usercodecount, @t_bhgs_sum, @isHBNum ,@isGZNum ,@isJMJENum
			while @@fetch_status=0
			begin
				begin transaction
				set @t_usercount=@t_usercodecount
				--insert into ucis_logs values(@t_sjhm,@t_usercount,@isHBNum)


--print '>5合并，抄表：'+@isGZNum+'，优惠：'+@isJMJENum+',手机：'+@t_sjhm
				--判断如果同一个手机号码存在超过5个用户编号是否合并，合并规则：同一用户编号并表示可以合并发送
				if @t_usercount>5 and @isHBNum >= @t_usercount
				begin
					select @t_bysf_sum=sum(bysf),@t_bypsf_sum=sum(bypsf),@t_byljf_sum=sum(byljf),@t_byje_sum=sum(byje),
					@t_tqje_sum=sum(case when tqje is null  then 0 else tqje end),
					@t_bhgs_sum=sum(case when bhgs is null then 1 else bhgs end),
					@t_jmje_sum=sum(reducedMoney)
					from ucis_sf_message_info where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '1'
					--费用合计
					set @t_fyhj=@t_byje_sum + @t_tqje_sum
					set @t_yhqsf_sum=@t_bysf_sum+@t_jmje_sum

					--判断如果是否有估抄表：是则 继续判断有无水价优惠；无水价优惠模板4 有水价优惠模板8
					if @isGZNum>0
					BEGIN
						if @isJMJENum=0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100004' and type='A00004'
						end
						ELSE if  @isJMJENum>0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100008' and type='A00004'
							set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_yhqsf_sum as varchar(20)))
							set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_jmje_sum as varchar(20)))
						end

						-- 工商特建户、且垃圾>0 OR 污水费>0）使用新缓缴模板
						if @t_bypsf_sum > 0 or @t_byljf_sum > 0
							BEGIN
								select @t_smsinfo=replace(content,
																	 '2023年XX月',
																	 cast(DATEPART(year, DATEADD(MONTH, 3, GETDATE())) as varchar) + '年' +
																	 cast(DATEPART(MONTH, DATEADD(MONTH, 3, GETDATE())) as varchar) + '月')
																	from smsmodel
																 where ID = '400005'
																	 and type = 'A00004'

							END
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_usercount)
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',cast(@t_bysf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_bypsf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_byljf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_tqje_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_fyhj as varchar(20)))
					end
					--判断如果是否有估抄表：否则 继续判断有无水价优惠；无水价优惠模板2 有水价优惠模板6
					ELSE if @isGZNum=0
					BEGIN
						if @isJMJENum=0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100002' and type='A00004'
						end
						ELSE if  @isJMJENum>0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100006' and type='A00004'
							set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_yhqsf_sum as varchar(20)))
							set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_jmje_sum as varchar(20)))
						end

						-- 工商特建户、且垃圾>0 OR 污水费>0）使用新缓缴模板
						if @t_bypsf_sum > 0 or @t_byljf_sum > 0
							BEGIN
								select @t_smsinfo=replace(content,
																	 '2023年XX月',
																	 cast(DATEPART(year, DATEADD(MONTH, 3, GETDATE())) as varchar) + '年' +
																	 cast(DATEPART(MONTH, DATEADD(MONTH, 3, GETDATE())) as varchar) + '月')
																	from smsmodel
																 where ID = '400005'
																	 and type = 'A00004'

							END
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_usercount)
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',cast(@t_bysf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_bypsf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_byljf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_tqje_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_fyhj as varchar(20)))
					end


					--写历史记录表
					begin
						Insert Into Ucis_Sf_Message_Info_His
							(Rec_Guid,
							 Nf,
							 Yf,
							 Sjhm,
							 Yhbh,
							 Ysdz,
							 Lcbrq,
							 Cbrq,
							 Ysl,
							 Bysf,
							 Bypsf,
							 Byljf,
							 Byje,
							 Tqje,
							 Bhgs,
							 Fslx,
							 Gss,
							 Result,
							 Sendtime,
							 Id,
							 Sfznj,
							 Psfznj,
							 Ljfznj,
							 Itemid,
							 Deptcode,
							 Doc_Guid,
							 Sms_Guid,smsmix,ReadingStatus,reducedMoney,recordstr,isgsjt)
							Select Newid(),
										 Nf,
										 Yf,
										 Sjhm,
										 Yhbh,
										 Ysdz,
										 Lcbrq,
										 Cbrq,
										 Ysl,
										 Bysf,
										 Bypsf,
										 Byljf,
										 Byje,
										 Tqje,
										 Bhgs,
										 Fslx,
										 Gss,
										 Result,
										 Sendtime,
										 Id,
										 Sfznj,
										 Psfznj,
										 Ljfznj,
										 Itemid,
										 Deptcode,
										 @t_Smsguid,
										 @Sms_Guid,smsmix,ReadingStatus,reducedMoney,recordstr,isgsjt
								From Ucis_Sf_Message_Info
							 Where Sjhm = @t_Sjhm
								 And Sms_Guid = @Sms_Guid and  isgsjt= '1'
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
					--写CJ_Comprehensive表
					begin
						declare cur_sjhma cursor for
						select nf,yf,sjhm,yhbh,ysdz,id,itemid,deptcode from ucis_sf_message_info where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '1'
						open cur_sjhma
						fetch next from cur_sjhma into @t_year,@t_month,@t_sjhm,@t_yhbh,@t_ysdz,@t_id,@t_itemid,@t_deptcode
						while @@fetch_status=0
						begin
							Insert Into Cj_Comprehensive_Ucis
								(Nf,
								 Yf,
								 Yhbh,
								 Sjhm,
								 Doc_Guid,
								 Sms_Comment,
								 Type_Id,
								 Ysdz,
								 Id,
								 Cj_Guid,
								 Sms_Guid,
								 Itemid,
								 Deptcode)
							Values
								(@t_Year,
								 @t_Month,
								 @t_Yhbh,
								 @t_Sjhm,
								 @t_Smsguid,
								 @t_Smsinfo,
								 'A00014',
								 @t_Ysdz,
								 @t_Id,
								 Newid(),
								 @Sms_Guid,
								 @t_Itemid,
								 @t_Deptcode)
							if @@error<>0
								begin
									rollback transaction
									goto nextpoint1
								end
							fetch next from cur_sjhma into @t_year,@t_month,@t_sjhm,@t_yhbh,@t_ysdz,@t_id,@t_itemid,@t_deptcode
						end
					close cur_sjhma
					deallocate cur_sjhma
					end
					--写发送表
					begin
						Insert Into Documentout_Ucis
							(Documentout_Guid,
							 Doctype,
							 Status,
							 Documentpath,
							 Documentto,
							 Createdby,
							 Createddate,
							 Expectedstartdate,
							 Stringfield1,
							 Stringfield2,
							 Trialcount,
							 Trialcountlimit,
							 Sms_Guid)
						Values
							(@t_Smsguid,
							 '3',
							 '0',
							 @t_Smsinfo,
							 @t_Sjhm,
							 'SYSTEM',
							 Getdate(),
							 null,
							 'A00014',
							 @t_Yhbh,
							 0,
							 3,
							 @Sms_Guid)

						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
						set @i_smsnum=@i_smsnum+1
					end
					--清除发送记录
					begin
						delete from ucis_sf_message_info where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '1'
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
				end

				--同一用户手机用户编号在5个以内，或超过5个但表示不能合并发送的逐条发送
				else if (@t_usercount>1 and  @t_usercount<=5) or (@t_usercount>5 and @isHBNum < @t_usercount)
				begin

--print '>5不合并，抄表：'+@t_cbzt+'，优惠：'+@t_jmje+',手机：'+@t_sjhm
					declare cur_sjhmin5 cursor for select id from ucis_sf_message_info where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '1'
					open cur_sjhmin5
					fetch next from cur_sjhmin5 into @t_id
					while @@fetch_status=0
					begin
						begin transaction
							Select @t_Yhbh = Yhbh,
								 @t_Ysdz = Ysdz,
								 @t_Lyear = Year(Lcbrq),
								 @t_Lmonth = Month(Lcbrq),
								 @t_Lday = Day(Lcbrq),
								 @t_Year = Year(Cbrq),
								 @t_Month = Month(Cbrq),
								 @t_Day = Day(Cbrq),
								 @t_Ysl = Ysl,
								 @t_Bysf = Bysf,
								 @t_Bypsf = Bypsf,
								 @t_Byljf = Byljf,
								 @t_Byje = Byje,
								 @t_cbzt = ReadingStatus,
								 @t_jmje = reducedMoney,
								 @t_recordstr = recordstr,
								 @t_Tqje = Case
									 When Tqje Is Null Then
										0
									 Else
										Tqje
								 End
						From Ucis_Sf_Message_Info
					 Where Id = @t_Id
						 And Sms_Guid = @Sms_Guid and  isgsjt= '1'
						--费用合计
						set @t_fyhj=@t_byje+@t_tqje
						set @t_yhqsf_sum=@t_Bysf+@t_jmje


					--判断如果是否有估抄表：是则 继续判断有无水价优惠；无水价优惠模板3 有水价优惠模板7
					if @t_cbzt=4
					BEGIN
						--if @t_jmje=0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='300015' and type='A00004'
						end

						-- 工商特建户、且垃圾>0 OR 污水费>0）使用新缓缴模板
						if @t_bypsf > 0 or @t_byljf > 0
							BEGIN
								select @t_smsinfo=replace(content,
																	 '2023年XX月',
																	 cast(DATEPART(year, DATEADD(MONTH, 3, GETDATE())) as varchar) + '年' +
																	 cast(DATEPART(MONTH, DATEADD(MONTH, 3, GETDATE())) as varchar) + '月')
																	from smsmodel
																 where ID = '400015'
																	 and type = 'A00004'

							END
/*0714
						ELSE if @t_jmje>0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100007' and type='A00004'
							set @t_smsinfo=REPLACE(@t_smsinfo,'nnn',cast(@t_jmje as varchar(20)))
							set @t_smsinfo=REPLACE(@t_smsinfo,'ooo',cast(@t_yhqsf_sum as varchar(20)))
						end
*/
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'recordstr',cast(@t_recordstr as varchar(2000)))
/*0714
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_lyear as varchar(4)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_lmonth as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_lday as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_month as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_day as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_ysl as varchar(20)))
*/
					---0412
					---set @t_smsinfo=REPLACE(@t_smsinfo,'iii',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'xxx',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'yyy',cast(@t_jmje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'jjj',cast(@t_bypsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'kkk',cast(@t_byljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'lll',cast(@t_tqje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'mmm',cast(@t_fyhj as varchar(20)))
					end
					--判断如果是否有估抄表：否则 继续判断有无水价优惠；无水价优惠模板1 有水价优惠模板5
					ELSE
					BEGIN
						--if @t_jmje=0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='300015' and type='A00004'
						end

						-- 工商特建户、且垃圾>0 OR 污水费>0）使用新缓缴模板
						if @t_bypsf > 0 or @t_byljf > 0
							BEGIN
								select @t_smsinfo=replace(content,
																	 '2023年XX月',
																	 cast(DATEPART(year, DATEADD(MONTH, 3, GETDATE())) as varchar) + '年' +
																	 cast(DATEPART(MONTH, DATEADD(MONTH, 3, GETDATE())) as varchar) + '月')
																	from smsmodel
																 where ID = '400015'
																	 and type = 'A00004'

							END
/*0714
						ELSE if  @t_jmje>0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100005' and type='A00004'
							set @t_smsinfo=REPLACE(@t_smsinfo,'nnn',cast(@t_jmje as varchar(20)))
							set @t_smsinfo=REPLACE(@t_smsinfo,'ooo',cast(@t_yhqsf_sum as varchar(20)))
						end
*/
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'recordstr',cast(@t_recordstr as varchar(2000)))
/*0714
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_lyear as varchar(4)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_lmonth as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_lday as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_month as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_day as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_ysl as varchar(20)))
*/
					---0412
					---set @t_smsinfo=REPLACE(@t_smsinfo,'iii',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'xxx',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'yyy',cast(@t_jmje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'jjj',cast(@t_bypsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'kkk',cast(@t_byljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'lll',cast(@t_tqje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'mmm',cast(@t_fyhj as varchar(20)))
					end

						--写历史记录表
						begin
							Insert Into Ucis_Sf_Message_Info_His
								(Rec_Guid,
								 Nf,
								 Yf,
								 Sjhm,
								 Yhbh,
								 Ysdz,
								 Lcbrq,
								 Cbrq,
								 Ysl,
								 Bysf,
								 Bypsf,
								 Byljf,
								 Byje,
								 Tqje,
								 Bhgs,
								 Fslx,
								 Gss,
								 Result,
								 Sendtime,
								 Id,
								 Sfznj,
								 Psfznj,
								 Ljfznj,
								 Itemid,
								 Deptcode,
								 Doc_Guid,
								 Sms_Guid,SmsMix,ReadingStatus,reducedMoney,recordstr,isgsjt)
								Select Newid(),
											 Nf,
											 Yf,
											 Sjhm,
											 Yhbh,
											 Ysdz,
											 Lcbrq,
											 Cbrq,
											 Ysl,
											 Bysf,
											 Bypsf,
											 Byljf,
											 Byje,
											 Tqje,
											 Bhgs,
											 Fslx,
											 Gss,
											 Result,
											 Sendtime,
											 Id,
											 Sfznj,
											 Psfznj,
											 Ljfznj,
											 Itemid,
											 Deptcode,
											 @t_Smsguid,
											 @Sms_Guid,SmsMix,ReadingStatus,reducedMoney,recordstr,isgsjt
									From Ucis_Sf_Message_Info
								 Where Id = @t_Id
									 And Sms_Guid = @Sms_Guid and  isgsjt= '1'

							if @@error<>0
							begin
								rollback transaction
								goto nextpoint2
							end
						end
						--写CJ_Comprehensive表
						begin
							Select @t_Year = Nf,
										 @t_Month = Yf,
										 @t_Sjhm = Sjhm,
										 @t_Yhbh = Yhbh,
										 @t_Ysdz = Ysdz,
										 @t_Id = Id,
										 @t_Itemid = Itemid,
										 @t_Deptcode = Deptcode
								From Ucis_Sf_Message_Info
							 Where Id = @t_Id
								 And Sms_Guid = @Sms_Guid and  isgsjt= '1'

							Insert Into Cj_Comprehensive_Ucis
								(Nf,
								 Yf,
								 Yhbh,
								 Sjhm,
								 Doc_Guid,
								 Sms_Comment,
								 Type_Id,
								 Ysdz,
								 Id,
								 Cj_Guid,
								 Sms_Guid,
								 Itemid,
								 Deptcode)
							Values
								(@t_Year,
								 @t_Month,
								 @t_Yhbh,
								 @t_Sjhm,
								 @t_Smsguid,
								 @t_Smsinfo,
								 'A00014',
								 @t_Ysdz,
								 @t_Id,
								 Newid(),
								 @Sms_Guid,
								 @t_Itemid,
								 @t_Deptcode)

							if @@error<>0
							begin
								rollback transaction
								goto nextpoint2
							end
						end
						--写发送表
						begin
							Insert Into Documentout_Ucis
								(Documentout_Guid,
								 Doctype,
								 Status,
								 Documentpath,
								 Documentto,
								 Createdby,
								 Createddate,
								 Expectedstartdate,
								 Stringfield1,
								 Stringfield2,
								 Trialcount,
								 Trialcountlimit,
								 Sms_Guid)
							Values
								(@t_Smsguid,
								 '3',
								 '0',
								 @t_Smsinfo,
								 @t_Sjhm,
								 'SYSTEM',
								 Getdate(),
								 null,
								 'A00014',
								 @t_Yhbh,
								 0,
								 3,
								 @Sms_Guid)

							if @@error<>0
							begin
								rollback transaction
								goto nextpoint2
							end
							set @i_smsnum=@i_smsnum+1
						end
						--清除发送记录
						begin
							delete from ucis_sf_message_info where  id=@t_id and sms_guid=@sms_guid and  isgsjt= '1'
							if @@error<>0
								begin
									rollback transaction
									goto nextpoint2
								end
						end
						nextpoint2:
						commit transaction
						fetch next from cur_sjhmin5 into @t_id
					end
					close cur_sjhmin5
					deallocate cur_sjhmin5
				end
				else if @t_usercount=1
				begin

--print '<5不合并，抄表：'+@t_cbzt+'，优惠：'+@t_jmje+',手机：'+@t_sjhm
					Select @t_Yhbh = Yhbh,
								 @t_Ysdz = Ysdz,
								 @t_Lyear = Year(Lcbrq),
								 @t_Lmonth = Month(Lcbrq),
								 @t_Lday = Day(Lcbrq),
								 @t_Year = Year(Cbrq),
								 @t_Month = Month(Cbrq),
								 @t_Day = Day(Cbrq),
								 @t_Ysl = Ysl,
								 @t_Bysf = Bysf,
								 @t_Bypsf = Bypsf,
								 @t_Byljf = Byljf,
								 @t_Byje = Byje,
								 @t_cbzt = ReadingStatus,
								 @t_jmje = reducedMoney,
								 @t_recordstr = recordstr,
								 @t_Tqje = Case
									 When Tqje Is Null Then
										0
									 Else
										Tqje
								 End
						From Ucis_Sf_Message_Info
					 Where Sjhm = @t_Sjhm
						 And Sms_Guid = @Sms_Guid and  isgsjt= '1'
					--费用合计
					set @t_fyhj=@t_byje+@t_tqje
					set @t_yhqsf_sum=@t_Bysf+@t_jmje

					--判断如果是否有估抄表：是则 继续判断有无水价优惠；无水价优惠模板3 有水价优惠模板7
					if @t_cbzt=4
					BEGIN
						--if @t_jmje=0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='300001' and type='A00004'
						end

						-- 工商特建户、且垃圾>0 OR 污水费>0）使用新缓缴模板
						if @t_bypsf > 0 or @t_byljf > 0
							BEGIN
								select @t_smsinfo=replace(content,
																	 '2023年XX月',
																	 cast(DATEPART(year, DATEADD(MONTH, 3, GETDATE())) as varchar) + '年' +
																	 cast(DATEPART(MONTH, DATEADD(MONTH, 3, GETDATE())) as varchar) + '月')
																	from smsmodel
																 where ID = '400001'
																	 and type = 'A00004'

							END
/*0714
						ELSE if @t_jmje>0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100007' and type='A00004'
							set @t_smsinfo=REPLACE(@t_smsinfo,'nnn',cast(@t_jmje as varchar(20)))
							set @t_smsinfo=REPLACE(@t_smsinfo,'ooo',cast(@t_yhqsf_sum as varchar(20)))
						end
*/
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'recordstr',cast(@t_recordstr as varchar(2000)))
/*
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_lyear as varchar(4)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_lmonth as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_lday as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_month as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_day as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_ysl as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'iii',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'jjj',cast(@t_bypsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'kkk',cast(@t_byljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'lll',cast(@t_tqje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'mmm',cast(@t_fyhj as varchar(20)))
*/

					---0412
					---set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'xxx',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'yyy',cast(@t_jmje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_bypsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_byljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_tqje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_fyhj as varchar(20)))
					end
					--判断如果是否有估抄表：否则 继续判断有无水价优惠；无水价优惠模板1 有水价优惠模板5
					ELSE
					BEGIN
						--if @t_jmje=0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='300001' and type='A00004'
						end

						-- 工商特建户、且垃圾>0 OR 污水费>0）使用新缓缴模板
						if @t_bypsf > 0 or @t_byljf > 0
							BEGIN
								select @t_smsinfo=replace(content,
																	 '2023年XX月',
																	 cast(DATEPART(year, DATEADD(MONTH, 3, GETDATE())) as varchar) + '年' +
																	 cast(DATEPART(MONTH, DATEADD(MONTH, 3, GETDATE())) as varchar) + '月')
																	from smsmodel
																 where ID = '400001'
																	 and type = 'A00004'

							END
/*0714
						ELSE if  @t_jmje>0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100005' and type='A00004'
							set @t_smsinfo=REPLACE(@t_smsinfo,'nnn',cast(@t_jmje as varchar(20)))
							set @t_smsinfo=REPLACE(@t_smsinfo,'ooo',cast(@t_yhqsf_sum as varchar(20)))
						end
*/
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'recordstr',cast(@t_recordstr as varchar(2000)))
/*0714
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_lyear as varchar(4)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_lmonth as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_lday as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_month as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_day as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_ysl as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'iii',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'jjj',cast(@t_bypsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'kkk',cast(@t_byljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'lll',cast(@t_tqje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'mmm',cast(@t_fyhj as varchar(20)))
*/
					---0412
					---set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'xxx',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'yyy',cast(@t_jmje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_bypsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_byljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_tqje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_fyhj as varchar(20)))
					end

					--写历史记录表
					begin
						Insert Into Ucis_Sf_Message_Info_His
							(Rec_Guid,
							 Nf,
							 Yf,
							 Sjhm,
							 Yhbh,
							 Ysdz,
							 Lcbrq,
							 Cbrq,
							 Ysl,
							 Bysf,
							 Bypsf,
							 Byljf,
							 Byje,
							 Tqje,
							 Bhgs,
							 Fslx,
							 Gss,
							 Result,
							 Sendtime,
							 Id,
							 Sfznj,
							 Psfznj,
							 Ljfznj,
							 Itemid,
							 Deptcode,
							 Doc_Guid,
							 Sms_Guid,
							 SmsMix,ReadingStatus,reducedMoney,recordstr,isgsjt)
							Select Newid(),
										 Nf,
										 Yf,
										 Sjhm,
										 Yhbh,
										 Ysdz,
										 Lcbrq,
										 Cbrq,
										 Ysl,
										 Bysf,
										 Bypsf,
										 Byljf,
										 Byje,
										 Tqje,
										 Bhgs,
										 Fslx,
										 Gss,
										 Result,
										 Sendtime,
										 Id,
										 Sfznj,
										 Psfznj,
										 Ljfznj,
										 Itemid,
										 Deptcode,
										 @t_Smsguid,
										 @Sms_Guid,
										 Smsmix,ReadingStatus,reducedMoney,recordstr,isgsjt
								From Ucis_Sf_Message_Info
							 Where Sjhm = @t_Sjhm
								 And Sms_Guid = @Sms_Guid and  isgsjt= '1'
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
					--写CJ_Comprehensive表
					begin
            Select @t_Year = Nf,
                   @t_Month = Yf,
                   @t_Sjhm = Sjhm,
                   @t_Yhbh = Yhbh,
                   @t_Ysdz = Ysdz,
                   @t_Id = Id,
                   @t_Itemid = Itemid,
                   @t_Deptcode = Deptcode
              From Ucis_Sf_Message_Info
             Where Sjhm = @t_Sjhm and  isgsjt= '1'

            Insert Into Cj_Comprehensive_Ucis
              (Nf,
               Yf,
               Yhbh,
               Sjhm,
               Doc_Guid,
               Sms_Comment,
               Type_Id,
               Ysdz,
               Id,
               Cj_Guid,
               Sms_Guid,
               Itemid,
               Deptcode)
            Values
              (@t_Year,
               @t_Month,
               @t_Yhbh,
               @t_Sjhm,
               @t_Smsguid,
               @t_Smsinfo,
               'A00014',
               @t_Ysdz,
               @t_Id,
               Newid(),
               @Sms_Guid,
               @t_Itemid,
               @t_Deptcode)

						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
					--写发送表
					begin
						Insert Into Documentout_Ucis
							(Documentout_Guid,
							 Doctype,
							 Status,
							 Documentpath,
							 Documentto,
							 Createdby,
							 Createddate,
							 Expectedstartdate,
							 Stringfield1,
							 Stringfield2,
							 Trialcount,
							 Trialcountlimit,
							 Sms_Guid)
						Values
							(@t_Smsguid,
							 '3',
							 '0',
							 @t_Smsinfo,
							 @t_Sjhm,
							 'SYSTEM',
							 Getdate(),
							 null,
							 'A00014',
							 @t_Yhbh,
							 0,
							 3,
							 @Sms_Guid)
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
						set @i_smsnum=@i_smsnum+1
					end
					--清除发送记录
					begin
						delete from ucis_sf_message_info where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '1'
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
				end;
				nextpoint1:
				commit transaction
				fetch next from cur_sjhm_a00014 into @t_sjhm, @t_usercodecount, @t_bhgs_sum, @isHBNum ,@isGZNum ,@isJMJENum
			end;
			close cur_sjhm_a00014
			deallocate cur_sjhm_a00014
---------------------------------------------------------isgsjt=0------------------------------------------------------------------

			--初始化当前短信数量
			set @i_smsnum=0

			--定义游标循环处理
			declare cur_sjhm_a000141 cursor for Select Sjhm,
				       Count(*),
				       (Case
				         When Bhgs Is Null Or Bhgs = '' Then
				          1
				         Else
				          Bhgs
				       End) As Bhgscount,
				       Sum(Case
				             When Smsmix = '1' Then
				              1
				             Else
				              0
				           End) Ishbnum,
							Sum(Case
				             When ReadingStatus = '4' Then
				              1
				             Else
				              0
				           End) isGZNum,
							Sum(Case
				             When reducedMoney >0 Then
				              1
				             Else
				              0
				           End) isJMJENum
				  From Ucis_Sf_Message_Info
				 Where Sms_Guid = @sms_guid
				   And Sjhm Is Not Null
				   And Sjhm != ''
				   And Len(Sjhm) = 11
					 and  isgsjt= '0'
				   --And Sjhm = '13600163620'
				 Group By Sjhm,
				          (Case
				            When Bhgs Is Null Or Bhgs = '' Then
				             1
				            Else
				             Bhgs
				          End);

			--处理发送记录
			open cur_sjhm_a000141
			fetch next from cur_sjhm_a000141 into @t_sjhm, @t_usercodecount, @t_bhgs_sum, @isHBNum ,@isGZNum ,@isJMJENum
			while @@fetch_status=0
			begin
				begin transaction
				set @t_usercount=@t_usercodecount
				--insert into ucis_logs values(@t_sjhm,@t_usercount,@isHBNum)


--print '>5合并，抄表：'+@isGZNum+'，优惠：'+@isJMJENum+',手机：'+@t_sjhm
				--判断如果同一个手机号码存在超过5个用户编号是否合并，合并规则：同一用户编号并表示可以合并发送
				if @t_usercount>5 and @isHBNum >= @t_usercount
				begin
					select @t_bysf_sum=sum(bysf),@t_bypsf_sum=sum(bypsf),@t_byljf_sum=sum(byljf),@t_byje_sum=sum(byje),
					@t_tqje_sum=sum(case when tqje is null  then 0 else tqje end),
					@t_bhgs_sum=sum(case when bhgs is null then 1 else bhgs end),
					@t_jmje_sum=sum(reducedMoney)
					from ucis_sf_message_info where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '0'
					--费用合计
					set @t_fyhj=@t_byje_sum + @t_tqje_sum
					set @t_yhqsf_sum=@t_bysf_sum+@t_jmje_sum

					--判断如果是否有估抄表：是则 继续判断有无水价优惠；无水价优惠模板4 有水价优惠模板8
					if @isGZNum>0
					BEGIN
						if @isJMJENum=0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100004' and type='A00004'
						end
						ELSE if  @isJMJENum>0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100008' and type='A00004'
							set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_yhqsf_sum as varchar(20)))
							set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_jmje_sum as varchar(20)))
						end
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_usercount)
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',cast(@t_bysf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_bypsf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_byljf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_tqje_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_fyhj as varchar(20)))
					end
					--判断如果是否有估抄表：否则 继续判断有无水价优惠；无水价优惠模板2 有水价优惠模板6
					ELSE if @isGZNum=0
					BEGIN
						if @isJMJENum=0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100002' and type='A00004'
						end
						ELSE if  @isJMJENum>0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100006' and type='A00004'
							set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_yhqsf_sum as varchar(20)))
							set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_jmje_sum as varchar(20)))
						end
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_usercount)
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',cast(@t_bysf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_bypsf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_byljf_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_tqje_sum as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_fyhj as varchar(20)))
					end


					--写历史记录表
					begin
						Insert Into Ucis_Sf_Message_Info_His
							(Rec_Guid,
							 Nf,
							 Yf,
							 Sjhm,
							 Yhbh,
							 Ysdz,
							 Lcbrq,
							 Cbrq,
							 Ysl,
							 Bysf,
							 Bypsf,
							 Byljf,
							 Byje,
							 Tqje,
							 Bhgs,
							 Fslx,
							 Gss,
							 Result,
							 Sendtime,
							 Id,
							 Sfznj,
							 Psfznj,
							 Ljfznj,
							 Itemid,
							 Deptcode,
							 Doc_Guid,
							 Sms_Guid,smsmix,ReadingStatus,reducedMoney,recordstr,isgsjt)
							Select Newid(),
										 Nf,
										 Yf,
										 Sjhm,
										 Yhbh,
										 Ysdz,
										 Lcbrq,
										 Cbrq,
										 Ysl,
										 Bysf,
										 Bypsf,
										 Byljf,
										 Byje,
										 Tqje,
										 Bhgs,
										 Fslx,
										 Gss,
										 Result,
										 Sendtime,
										 Id,
										 Sfznj,
										 Psfznj,
										 Ljfznj,
										 Itemid,
										 Deptcode,
										 @t_Smsguid,
										 @Sms_Guid,smsmix,ReadingStatus,reducedMoney,recordstr,isgsjt
								From Ucis_Sf_Message_Info
							 Where Sjhm = @t_Sjhm
								 And Sms_Guid = @Sms_Guid and  isgsjt= '0'
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
					--写CJ_Comprehensive表
					begin
						declare cur_sjhma cursor for
						select nf,yf,sjhm,yhbh,ysdz,id,itemid,deptcode from ucis_sf_message_info where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '0'
						open cur_sjhma
						fetch next from cur_sjhma into @t_year,@t_month,@t_sjhm,@t_yhbh,@t_ysdz,@t_id,@t_itemid,@t_deptcode
						while @@fetch_status=0
						begin
							Insert Into Cj_Comprehensive_Ucis
								(Nf,
								 Yf,
								 Yhbh,
								 Sjhm,
								 Doc_Guid,
								 Sms_Comment,
								 Type_Id,
								 Ysdz,
								 Id,
								 Cj_Guid,
								 Sms_Guid,
								 Itemid,
								 Deptcode)
							Values
								(@t_Year,
								 @t_Month,
								 @t_Yhbh,
								 @t_Sjhm,
								 @t_Smsguid,
								 @t_Smsinfo,
								 'A00014',
								 @t_Ysdz,
								 @t_Id,
								 Newid(),
								 @Sms_Guid,
								 @t_Itemid,
								 @t_Deptcode)
							if @@error<>0
								begin
									rollback transaction
									goto nextpoint1
								end
							fetch next from cur_sjhma into @t_year,@t_month,@t_sjhm,@t_yhbh,@t_ysdz,@t_id,@t_itemid,@t_deptcode
						end
					close cur_sjhma
					deallocate cur_sjhma
					end
					--写发送表
					begin
						Insert Into Documentout_Ucis
							(Documentout_Guid,
							 Doctype,
							 Status,
							 Documentpath,
							 Documentto,
							 Createdby,
							 Createddate,
							 Expectedstartdate,
							 Stringfield1,
							 Stringfield2,
							 Trialcount,
							 Trialcountlimit,
							 Sms_Guid)
						Values
							(@t_Smsguid,
							 '3',
							 '0',
							 @t_Smsinfo,
							 @t_Sjhm,
							 'SYSTEM',
							 Getdate(),
							 null,
							 'A00014',
							 @t_Yhbh,
							 0,
							 3,
							 @Sms_Guid)

						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
						set @i_smsnum=@i_smsnum+1
					end
					--清除发送记录
					begin
						delete from ucis_sf_message_info where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '0'
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
				end

				--同一用户手机用户编号在5个以内，或超过5个但表示不能合并发送的逐条发送
				else if (@t_usercount>1 and  @t_usercount<=5) or (@t_usercount>5 and @isHBNum < @t_usercount)
				begin

--print '>5不合并，抄表：'+@t_cbzt+'，优惠：'+@t_jmje+',手机：'+@t_sjhm
					declare cur_sjhmin5 cursor for select id from ucis_sf_message_info where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '0'
					open cur_sjhmin5
					fetch next from cur_sjhmin5 into @t_id
					while @@fetch_status=0
					begin
						begin transaction
							Select @t_Yhbh = Yhbh,
								 @t_Ysdz = Ysdz,
								 @t_Lyear = Year(Lcbrq),
								 @t_Lmonth = Month(Lcbrq),
								 @t_Lday = Day(Lcbrq),
								 @t_Year = Year(Cbrq),
								 @t_Month = Month(Cbrq),
								 @t_Day = Day(Cbrq),
								 @t_Ysl = Ysl,
								 @t_Bysf = Bysf,
								 @t_Bypsf = Bypsf,
								 @t_Byljf = Byljf,
								 @t_Byje = Byje,
								 @t_cbzt = ReadingStatus,
								 @t_jmje = reducedMoney,
								 @t_recordstr = recordstr,
								 @t_Tqje = Case
									 When Tqje Is Null Then
										0
									 Else
										Tqje
								 End
						From Ucis_Sf_Message_Info
					 Where Id = @t_Id
						 And Sms_Guid = @Sms_Guid and  isgsjt= '0'
						--费用合计
						set @t_fyhj=@t_byje+@t_tqje
						set @t_yhqsf_sum=@t_Bysf+@t_jmje


					--判断如果是否有估抄表：是则 继续判断有无水价优惠；无水价优惠模板3 有水价优惠模板7
					if @t_cbzt=4
					BEGIN
						--if @t_jmje=0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='200015' and type='A00004'
						end
/*0714
						ELSE if @t_jmje>0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100007' and type='A00004'
							set @t_smsinfo=REPLACE(@t_smsinfo,'nnn',cast(@t_jmje as varchar(20)))
							set @t_smsinfo=REPLACE(@t_smsinfo,'ooo',cast(@t_yhqsf_sum as varchar(20)))
						end
*/
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'recordstr',cast(@t_recordstr as varchar(2000)))
/*0714
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_lyear as varchar(4)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_lmonth as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_lday as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_month as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_day as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_ysl as varchar(20)))
*/
					---0412
					set @t_smsinfo=REPLACE(@t_smsinfo,'iii',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'jjj',cast(@t_bypsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'kkk',cast(@t_byljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'lll',cast(@t_tqje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'mmm',cast(@t_fyhj as varchar(20)))
					end
					--判断如果是否有估抄表：否则 继续判断有无水价优惠；无水价优惠模板1 有水价优惠模板5
					ELSE
					BEGIN
						--if @t_jmje=0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='200015' and type='A00004'
						end
/*0714
						ELSE if  @t_jmje>0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100005' and type='A00004'
							set @t_smsinfo=REPLACE(@t_smsinfo,'nnn',cast(@t_jmje as varchar(20)))
							set @t_smsinfo=REPLACE(@t_smsinfo,'ooo',cast(@t_yhqsf_sum as varchar(20)))
						end
*/
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'recordstr',cast(@t_recordstr as varchar(2000)))
/*0714
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_lyear as varchar(4)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_lmonth as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_lday as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_month as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_day as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_ysl as varchar(20)))
*/
					---0412
					set @t_smsinfo=REPLACE(@t_smsinfo,'iii',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'jjj',cast(@t_bypsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'kkk',cast(@t_byljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'lll',cast(@t_tqje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'mmm',cast(@t_fyhj as varchar(20)))
					end

						--写历史记录表
						begin
							Insert Into Ucis_Sf_Message_Info_His
								(Rec_Guid,
								 Nf,
								 Yf,
								 Sjhm,
								 Yhbh,
								 Ysdz,
								 Lcbrq,
								 Cbrq,
								 Ysl,
								 Bysf,
								 Bypsf,
								 Byljf,
								 Byje,
								 Tqje,
								 Bhgs,
								 Fslx,
								 Gss,
								 Result,
								 Sendtime,
								 Id,
								 Sfznj,
								 Psfznj,
								 Ljfznj,
								 Itemid,
								 Deptcode,
								 Doc_Guid,
								 Sms_Guid,SmsMix,ReadingStatus,reducedMoney,recordstr,isgsjt)
								Select Newid(),
											 Nf,
											 Yf,
											 Sjhm,
											 Yhbh,
											 Ysdz,
											 Lcbrq,
											 Cbrq,
											 Ysl,
											 Bysf,
											 Bypsf,
											 Byljf,
											 Byje,
											 Tqje,
											 Bhgs,
											 Fslx,
											 Gss,
											 Result,
											 Sendtime,
											 Id,
											 Sfznj,
											 Psfznj,
											 Ljfznj,
											 Itemid,
											 Deptcode,
											 @t_Smsguid,
											 @Sms_Guid,SmsMix,ReadingStatus,reducedMoney,recordstr,isgsjt
									From Ucis_Sf_Message_Info
								 Where Id = @t_Id
									 And Sms_Guid = @Sms_Guid and  isgsjt= '0'

							if @@error<>0
							begin
								rollback transaction
								goto nextpoint2
							end
						end
						--写CJ_Comprehensive表
						begin
							Select @t_Year = Nf,
										 @t_Month = Yf,
										 @t_Sjhm = Sjhm,
										 @t_Yhbh = Yhbh,
										 @t_Ysdz = Ysdz,
										 @t_Id = Id,
										 @t_Itemid = Itemid,
										 @t_Deptcode = Deptcode
								From Ucis_Sf_Message_Info
							 Where Id = @t_Id
								 And Sms_Guid = @Sms_Guid and  isgsjt= '0'

							Insert Into Cj_Comprehensive_Ucis
								(Nf,
								 Yf,
								 Yhbh,
								 Sjhm,
								 Doc_Guid,
								 Sms_Comment,
								 Type_Id,
								 Ysdz,
								 Id,
								 Cj_Guid,
								 Sms_Guid,
								 Itemid,
								 Deptcode)
							Values
								(@t_Year,
								 @t_Month,
								 @t_Yhbh,
								 @t_Sjhm,
								 @t_Smsguid,
								 @t_Smsinfo,
								 'A00014',
								 @t_Ysdz,
								 @t_Id,
								 Newid(),
								 @Sms_Guid,
								 @t_Itemid,
								 @t_Deptcode)

							if @@error<>0
							begin
								rollback transaction
								goto nextpoint2
							end
						end
						--写发送表
						begin
							Insert Into Documentout_Ucis
								(Documentout_Guid,
								 Doctype,
								 Status,
								 Documentpath,
								 Documentto,
								 Createdby,
								 Createddate,
								 Expectedstartdate,
								 Stringfield1,
								 Stringfield2,
								 Trialcount,
								 Trialcountlimit,
								 Sms_Guid)
							Values
								(@t_Smsguid,
								 '3',
								 '0',
								 @t_Smsinfo,
								 @t_Sjhm,
								 'SYSTEM',
								 Getdate(),
								 null,
								 'A00014',
								 @t_Yhbh,
								 0,
								 3,
								 @Sms_Guid)

							if @@error<>0
							begin
								rollback transaction
								goto nextpoint2
							end
							set @i_smsnum=@i_smsnum+1
						end
						--清除发送记录
						begin
							delete from ucis_sf_message_info where  id=@t_id and sms_guid=@sms_guid and  isgsjt= '0'
							if @@error<>0
								begin
									rollback transaction
									goto nextpoint3
								end
						end
						nextpoint3:
						commit transaction
						fetch next from cur_sjhmin5 into @t_id
					end
					close cur_sjhmin5
					deallocate cur_sjhmin5
				end
				else if @t_usercount=1
				begin

--print '<5不合并，抄表：'+@t_cbzt+'，优惠：'+@t_jmje+',手机：'+@t_sjhm
					Select @t_Yhbh = Yhbh,
								 @t_Ysdz = Ysdz,
								 @t_Lyear = Year(Lcbrq),
								 @t_Lmonth = Month(Lcbrq),
								 @t_Lday = Day(Lcbrq),
								 @t_Year = Year(Cbrq),
								 @t_Month = Month(Cbrq),
								 @t_Day = Day(Cbrq),
								 @t_Ysl = Ysl,
								 @t_Bysf = Bysf,
								 @t_Bypsf = Bypsf,
								 @t_Byljf = Byljf,
								 @t_Byje = Byje,
								 @t_cbzt = ReadingStatus,
								 @t_jmje = reducedMoney,
								 @t_recordstr = recordstr,
								 @t_Tqje = Case
									 When Tqje Is Null Then
										0
									 Else
										Tqje
								 End
						From Ucis_Sf_Message_Info
					 Where Sjhm = @t_Sjhm
						 And Sms_Guid = @Sms_Guid and  isgsjt= '0'
					--费用合计
					set @t_fyhj=@t_byje+@t_tqje
					set @t_yhqsf_sum=@t_Bysf+@t_jmje

					--判断如果是否有估抄表：是则 继续判断有无水价优惠；无水价优惠模板3 有水价优惠模板7
					if @t_cbzt=4
					BEGIN
						--if @t_jmje=0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='200001' and type='A00004'
						end
/*0714
						ELSE if @t_jmje>0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100007' and type='A00004'
							set @t_smsinfo=REPLACE(@t_smsinfo,'nnn',cast(@t_jmje as varchar(20)))
							set @t_smsinfo=REPLACE(@t_smsinfo,'ooo',cast(@t_yhqsf_sum as varchar(20)))
						end
*/
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'recordstr',cast(@t_recordstr as varchar(2000)))
/*
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_lyear as varchar(4)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_lmonth as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_lday as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_month as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_day as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_ysl as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'iii',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'jjj',cast(@t_bypsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'kkk',cast(@t_byljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'lll',cast(@t_tqje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'mmm',cast(@t_fyhj as varchar(20)))
*/

					---0412
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_bypsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_byljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_tqje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_fyhj as varchar(20)))
					end
					--判断如果是否有估抄表：否则 继续判断有无水价优惠；无水价优惠模板1 有水价优惠模板5
					ELSE
					BEGIN
						--if @t_jmje=0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='200001' and type='A00004'
						end
/*0714
						ELSE if  @t_jmje>0
						BEGIN
							select @t_smsinfo=content from smsmodel where ID='100005' and type='A00004'
							set @t_smsinfo=REPLACE(@t_smsinfo,'nnn',cast(@t_jmje as varchar(20)))
							set @t_smsinfo=REPLACE(@t_smsinfo,'ooo',cast(@t_yhqsf_sum as varchar(20)))
						end
*/
					set @t_smsguid=newid()
					set @t_smsinfo=REPLACE(@t_smsinfo,'aaa',@t_yhbh)
					set @t_ysdz=REPLACE(@t_ysdz,' ','')
					set @t_smsinfo=REPLACE(@t_smsinfo,'bbb',@t_ysdz)
					set @t_smsinfo=REPLACE(@t_smsinfo,'recordstr',cast(@t_recordstr as varchar(2000)))
/*0714
					set @t_smsinfo=REPLACE(@t_smsinfo,'ccc',cast(@t_lyear as varchar(4)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_lmonth as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_lday as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_month as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_day as varchar(2)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_ysl as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'iii',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'jjj',cast(@t_bypsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'kkk',cast(@t_byljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'lll',cast(@t_tqje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'mmm',cast(@t_fyhj as varchar(20)))
*/
					---0412
					set @t_smsinfo=REPLACE(@t_smsinfo,'ddd',cast(@t_bysf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'eee',cast(@t_bypsf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'fff',cast(@t_byljf as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'ggg',cast(@t_tqje as varchar(20)))
					set @t_smsinfo=REPLACE(@t_smsinfo,'hhh',cast(@t_fyhj as varchar(20)))
					end

					--写历史记录表
					begin
						Insert Into Ucis_Sf_Message_Info_His
							(Rec_Guid,
							 Nf,
							 Yf,
							 Sjhm,
							 Yhbh,
							 Ysdz,
							 Lcbrq,
							 Cbrq,
							 Ysl,
							 Bysf,
							 Bypsf,
							 Byljf,
							 Byje,
							 Tqje,
							 Bhgs,
							 Fslx,
							 Gss,
							 Result,
							 Sendtime,
							 Id,
							 Sfznj,
							 Psfznj,
							 Ljfznj,
							 Itemid,
							 Deptcode,
							 Doc_Guid,
							 Sms_Guid,
							 SmsMix,ReadingStatus,reducedMoney,recordstr,isgsjt)
							Select Newid(),
										 Nf,
										 Yf,
										 Sjhm,
										 Yhbh,
										 Ysdz,
										 Lcbrq,
										 Cbrq,
										 Ysl,
										 Bysf,
										 Bypsf,
										 Byljf,
										 Byje,
										 Tqje,
										 Bhgs,
										 Fslx,
										 Gss,
										 Result,
										 Sendtime,
										 Id,
										 Sfznj,
										 Psfznj,
										 Ljfznj,
										 Itemid,
										 Deptcode,
										 @t_Smsguid,
										 @Sms_Guid,
										 Smsmix,ReadingStatus,reducedMoney,recordstr,isgsjt
								From Ucis_Sf_Message_Info
							 Where Sjhm = @t_Sjhm
								 And Sms_Guid = @Sms_Guid and  isgsjt= '0'
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
					--写CJ_Comprehensive表
					begin
            Select @t_Year = Nf,
                   @t_Month = Yf,
                   @t_Sjhm = Sjhm,
                   @t_Yhbh = Yhbh,
                   @t_Ysdz = Ysdz,
                   @t_Id = Id,
                   @t_Itemid = Itemid,
                   @t_Deptcode = Deptcode
              From Ucis_Sf_Message_Info
             Where Sjhm = @t_Sjhm and  isgsjt= '0'

            Insert Into Cj_Comprehensive_Ucis
              (Nf,
               Yf,
               Yhbh,
               Sjhm,
               Doc_Guid,
               Sms_Comment,
               Type_Id,
               Ysdz,
               Id,
               Cj_Guid,
               Sms_Guid,
               Itemid,
               Deptcode)
            Values
              (@t_Year,
               @t_Month,
               @t_Yhbh,
               @t_Sjhm,
               @t_Smsguid,
               @t_Smsinfo,
               'A00014',
               @t_Ysdz,
               @t_Id,
               Newid(),
               @Sms_Guid,
               @t_Itemid,
               @t_Deptcode)

						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
					end
					--写发送表
					begin
						Insert Into Documentout_Ucis
							(Documentout_Guid,
							 Doctype,
							 Status,
							 Documentpath,
							 Documentto,
							 Createdby,
							 Createddate,
							 Expectedstartdate,
							 Stringfield1,
							 Stringfield2,
							 Trialcount,
							 Trialcountlimit,
							 Sms_Guid)
						Values
							(@t_Smsguid,
							 '3',
							 '0',
							 @t_Smsinfo,
							 @t_Sjhm,
							 'SYSTEM',
							 Getdate(),
							 null,
							 'A00014',
							 @t_Yhbh,
							 0,
							 3,
							 @Sms_Guid)
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint1
						end
						set @i_smsnum=@i_smsnum+1
					end
					--清除发送记录
					begin
						delete from ucis_sf_message_info where sjhm=@t_sjhm and sms_guid=@sms_guid and  isgsjt= '0'
						if @@error<>0
						begin
							rollback transaction
							goto nextpoint5
						end
					end
				end;
				nextpoint5:
				commit transaction
				fetch next from cur_sjhm_a000141 into @t_sjhm, @t_usercodecount, @t_bhgs_sum, @isHBNum ,@isGZNum ,@isJMJENum
			end;
			close cur_sjhm_a000141
			deallocate cur_sjhm_a000141

---------------------------------------------------------isgsjt=0------------------------------------------------------------------

			begin

				select @doc_count=count(*) from DOCUMENTOUT_UCIS where sms_guid=@sms_guid
				select @cj_count=COUNT(*) from CJ_Comprehensive_UCIS where sms_guid=@sms_guid
				update ucis_sms_monitoring set doc_sum=@doc_count,cj_sum=@cj_count,doc_endtime=GETDATE(),cj_endtime=GETDATE(),smsfetch_status=1 where SMS_GUID=@sms_guid
			end
			begin
			  update ucis_smssendplan set STATUS=2 where rec_guid=@sms_guid
		    update ucis_sms_grab set status1='1'  where type_id='A00014' and status1 is null AND SMS_GUID = @sms_guid
				update CJ_Comprehensive_UCIS set createdate = GETDATE() where sms_guid=@sms_guid
			end
				BEGIN
					select top 1 @tguid = REC_GUID,@sname = sms_name from ucis_smssendplan where rec_guid = @sms_guid
					INSERT INTO message_operation_his VALUES (@tguid,@sname,'job', GETDATE(), '内容拼接',NEWID());
				END

				--审核通知短信
				begin
					select @sms_content=sms_name+'，短信拼接已完成，时间为'+CONVERT(varchar(30),GETDATE(),120)+'，请及时前往计划页面配置发送时间、分批设置。【深圳布吉水司】',@deptcode = deptcode  from ucis_smssendplan
					where type_id='A00014' and rec_guid=@sms_guid
					if @sms_content!='' or @sms_content is not null
					begin
						declare sms_audits_A00014 cursor for select phone from sms_audits where area = @deptcode
						open sms_audits_A00014
						fetch next from sms_audits_A00014 into @sm_phone
						while @@fetch_status=0
						begin
						SELECT @sms_content_time=CASE WHEN GETDATE() BETWEEN
             Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())+' '+'09:00:00'
             AND
             Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())+' '+'23:59:59'
             THEN GETDATE() ELSE Datename(year,GetDate())+'-'+Datename
             (month,GetDate())+'-'+Datename(day,GetDate())+' '+'09:00:00' END
							begin
							--insert into ITSV.szwaterDB.DBO.gxsendlist (content,sendtime,loginname,mobile,typework,bh,status,isbirthday,depid)
							--values(@sms_content,GETDATE(),'test',@sm_phone,'UCIS预通知','SZWG','0','0',1)
							insert into ITSV.szwaterDB.DBO.gxsendlist (content,sendtime,loginname,mobile,typework,bh,status,isbirthday,depid,schtime)
							values(@sms_content,@sms_content_time,'test',@sm_phone,'UCIS预通知','SZWG','0','0',1,@sms_content_time)
							end
							fetch next from sms_audits_A00014 into @sm_phone
						end
						close sms_audits_A00014
						deallocate sms_audits_A00014
					end
				end
		end
	end;

	End Try

	Begin Catch

		Exec Proc_Error_Record 'Proc_S2_DealDataToDocum_A00014'

	End Catch
End